{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NSS Tutorial\n",
    "\n",
    "Field\t| Type\n",
    "--------|-------\n",
    "ukprn\t| varchar(8)\n",
    "institution\t| varchar(100)\n",
    "subject\t| varchar(60)\n",
    "level\t| varchar(50)\n",
    "question\t| varchar(10)\n",
    "A_STRONGLY_DISAGREE\t| int(11)\n",
    "A_DISAGREE\t| int(11)\n",
    "A_NEUTRAL\t| int(11)\n",
    "A_AGREE\t| int(11)\n",
    "A_STRONGLY_AGREE\t| int(11)\n",
    "A_NA\t| int(11)\n",
    "CI_MIN\t| int(11)\n",
    "score\t| int(11)\n",
    "CI_MAX\t| int(11)\n",
    "response\t| int(11)\n",
    "sample\t| int(11)\n",
    "aggregate\t| char(1)\n",
    "\n",
    "National Student Survey 2012\n",
    "\n",
    "The National Student Survey <http://www.thestudentsurvey.com/> is presented to thousands of graduating students in UK Higher Education. The survey asks 22 questions, students can respond with STRONGLY DISAGREE, DISAGREE, NEUTRAL, AGREE or STRONGLY AGREE. The values in these columns represent PERCENTAGES of the total students who responded with that answer.\n",
    "\n",
    "The table `nss` has one row per institution, subject and question."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prerequesites\n",
    "from pyhive import hive\n",
    "%load_ext sql\n",
    "%sql hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Check out one row\n",
    "\n",
    "The example shows the number who responded for:\n",
    "\n",
    "- question 1\n",
    "- at 'Edinburgh Napier University'\n",
    "- studying '(8) Computer Science'\n",
    "\n",
    "**Show the the percentage who STRONGLY AGREE**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>a_strongly_agree</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(23,)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT A_STRONGLY_AGREE\n",
    "  FROM nss\n",
    "    WHERE question='Q01'\n",
    "    AND institution='Edinburgh Napier University'\n",
    "    AND subject='(8) Computer Science'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Calculate how many agree or strongly agree\n",
    "\n",
    "**Show the institution and subject where the score is at least 100 for question 15.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>institution</th>\n",
       "        <th>subject</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kingston College</td>\n",
       "        <td>(I) Education</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Royal Holloway, University of London</td>\n",
       "        <td>(L) Geographical Studies</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Solihull College</td>\n",
       "        <td>(I) Education</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Stafford College</td>\n",
       "        <td>(D) Business and Administrative studies</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>University of Southampton</td>\n",
       "        <td>(E) Mass Communications and Documentation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>University of Wolverhampton</td>\n",
       "        <td>(7) Mathematical Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>University of Leicester</td>\n",
       "        <td>(2) Subjects allied to Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>University of Newcastle upon Tyne</td>\n",
       "        <td>(E) Mass Communications and Documentation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bishop Grosseteste University College, Lincoln</td>\n",
       "        <td>(F) Languages</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Universities of East Anglia and Essex; Joint Provision at University Campus Suffolk</td>\n",
       "        <td>(G) Historical and Philosophical studies</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Kingston College', '(I) Education'),\n",
       " ('Royal Holloway, University of London', '(L) Geographical Studies'),\n",
       " ('Solihull College', '(I) Education'),\n",
       " ('Stafford College', '(D) Business and Administrative studies'),\n",
       " ('University of Southampton', '(E) Mass Communications and Documentation'),\n",
       " ('University of Wolverhampton', '(7) Mathematical Sciences'),\n",
       " ('University of Leicester', '(2) Subjects allied to Medicine'),\n",
       " ('University of Newcastle upon Tyne', '(E) Mass Communications and Documentation'),\n",
       " ('Bishop Grosseteste University College, Lincoln', '(F) Languages'),\n",
       " ('Universities of East Anglia and Essex; Joint Provision at University Campus Suffolk', '(G) Historical and Philosophical studies')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT institution, subject\n",
    "  FROM nss\n",
    "    WHERE question='Q15'\n",
    "    AND score>=100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Unhappy Computer Students\n",
    "\n",
    "**Show the institution and score where the score for '(8) Computer Science' is less than 50 for question 'Q15'**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>institution</th>\n",
       "        <th>score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Blackburn College</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>North Lindsey College</td>\n",
       "        <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plymouth College of Art</td>\n",
       "        <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Somerset College of Arts and Technology</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>University of Wales, Newport</td>\n",
       "        <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Universities of East Anglia and Essex; Joint Provision at University Campus Suffolk</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Blackburn College', 45),\n",
       " ('North Lindsey College', 30),\n",
       " ('Plymouth College of Art', 47),\n",
       " ('Somerset College of Arts and Technology', 48),\n",
       " ('University of Wales, Newport', 30),\n",
       " ('Universities of East Anglia and Essex; Joint Provision at University Campus Suffolk', 45)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT institution, score\n",
    "  FROM nss\n",
    "    WHERE question='Q15'\n",
    "    AND subject='(8) Computer Science'\n",
    "    AND score<50"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. More Computing or Creative Students?\n",
    "\n",
    "**Show the subject and total number of students who responded to question 22 for each of the subjects '(8) Computer Science' and '(H) Creative Arts and Design'.**\n",
    "\n",
    "> _HINT_    \n",
    "> You will need to use SUM over the response column and GROUP BY subject"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>subject</th>\n",
       "        <th>tot_response</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>(8) Computer Science</td>\n",
       "        <td>10252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>(H) Creative Arts and Design</td>\n",
       "        <td>33336</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('(8) Computer Science', 10252), ('(H) Creative Arts and Design', 33336)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT subject, SUM(response) tot_response\n",
    "  FROM nss\n",
    "    WHERE question='Q22'\n",
    "    AND subject IN ('(8) Computer Science', '(H) Creative Arts and Design')\n",
    "    GROUP BY subject"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Strongly Agree Numbers\n",
    "\n",
    "**Show the subject and total number of students who A_STRONGLY_AGREE to question 22 for each of the subjects '(8) Computer Science' and '(H) Creative Arts and Design'.**\n",
    "\n",
    "> _HINT_    \n",
    "> The A_STRONGLY_AGREE column is a percentage. To work out the total number of students who strongly agree you must multiply this percentage by the number who responded (response) and divide by 100 - take the SUM of that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>subject</th>\n",
       "        <th>n_strongly_agree</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>(8) Computer Science</td>\n",
       "        <td>3421.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>(H) Creative Arts and Design</td>\n",
       "        <td>12107.539999999988</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('(8) Computer Science', 3421.22),\n",
       " ('(H) Creative Arts and Design', 12107.539999999988)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT subject, sum(response * A_STRONGLY_AGREE/100) n_strongly_agree\n",
    "  FROM nss\n",
    "    WHERE question='Q22'\n",
    "    AND subject IN ('(8) Computer Science', '(H) Creative Arts and Design')\n",
    "    GROUP BY subject"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Strongly Agree, Percentage\n",
    "\n",
    "**Show the percentage of students who A_STRONGLY_AGREE to question 22 for the subject '(8) Computer Science' show the same figure for the subject '(H) Creative Arts and Design'.**\n",
    "\n",
    "Use the **ROUND** function to show the percentage without decimal places."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>subject</th>\n",
       "        <th>pct_strongly_agree</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>(8) Computer Science</td>\n",
       "        <td>33.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>(H) Creative Arts and Design</td>\n",
       "        <td>36.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('(8) Computer Science', 33.0), ('(H) Creative Arts and Design', 36.0)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT subject, ROUND(SUM(A_STRONGLY_AGREE * response) / SUM(response)) pct_strongly_agree\n",
    "  FROM nss\n",
    "    WHERE question='Q22'\n",
    "    AND subject IN ('(8) Computer Science', '(H) Creative Arts and Design')\n",
    "    GROUP BY subject"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Scores for Institutions in Manchester\n",
    "\n",
    "**Show the average scores for question 'Q22' for each institution that include 'Manchester' in the name.**\n",
    "\n",
    "The column **score** is a percentage - you must use the method outlined above to multiply the percentage by the **response** and divide by the total response. Give your answer rounded to the nearest whole number."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>institution</th>\n",
       "        <th>score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Manchester Metropolitan University</td>\n",
       "        <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Manchester College</td>\n",
       "        <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>University of Manchester</td>\n",
       "        <td>83.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Manchester Metropolitan University', 81.0),\n",
       " ('The Manchester College', 72.0),\n",
       " ('University of Manchester', 83.0)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT institution, ROUND(SUM(response * score)/SUM(response)) score\n",
    "  FROM nss\n",
    "    WHERE question='Q22'\n",
    "    AND (institution LIKE '%Manchester%')\n",
    "    GROUP BY institution"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.Number of Computing Students in Manchester\n",
    "\n",
    "**Show the institution, the total sample size and the number of computing students for institutions in Manchester for 'Q01'.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>institution</th>\n",
       "        <th>_c1</th>\n",
       "        <th>comp</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Manchester Metropolitan University</td>\n",
       "        <td>6994</td>\n",
       "        <td>310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Manchester College</td>\n",
       "        <td>537</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>University of Manchester</td>\n",
       "        <td>8065</td>\n",
       "        <td>180</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Manchester Metropolitan University', 6994, 310),\n",
       " ('The Manchester College', 537, 46),\n",
       " ('University of Manchester', 8065, 180)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT institution, SUM(sample),SUM(\n",
    "    CASE WHEN subject='(8) Computer Science' THEN sample ELSE 0 END) comp\n",
    "FROM nss\n",
    "  WHERE question='Q01'\n",
    "    AND (institution LIKE '%Manchester%')\n",
    "    GROUP BY institution"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
